<?php
// $Id$

include_once('BaseModel.class.php');

class AddressBookModel extends BaseModel {
    function collect($offset=null,$limit=null,$search=array(),$by='1',$sort='') {
        $where = ($search) ? "WHERE LOWER({$search['searchfield']}) LIKE '%{$search['searchtext']}%'" : "WHERE 1";

        if (is_null($offset)) {
            $query = "SELECT COUNT(*) FROM members $where AND active=1";
        } else {
            $query = "SELECT * FROM members $where AND active=1 ORDER BY $by $sort LIMIT $offset,$limit";
        }
        return $this->query($query, true);
    }

    function export() {
        $query = "SELECT * FROM members
                 LEFT JOIN group_members on group_members.member_id =members.id
                 JOIN groups on group_members.group_id = groups.id
                 WHERE  members.active=1 GROUP BY members.id";
        return $this->query($query, true);
    }

    function import($list) {
        //make sure  check templates/AddressBookExport.tmpl.php
        if(!is_array($list[0]) ||  !isset($list[0]['Group'])) die('Cannot deciper.. please download sample file and follow format for uploading of contacts');
        foreach($list as $val) {
            $group = $val['Group'];

            if ($group) {
                $group = $this->select_one("SELECT id FROM `groups` WHERE `name` LIKE '$group' LIMIT 1");
            }

            //$msisdn = $val['Number'];
            //$firstname = $val['FirstName'];
            //$sql = "DELETE FROM members where firstname='$firstname' AND msisdn='$msisdn'";
            //$this->query($sql);
            $header = array(
                          "Student No" => "stud_no",
                          "Level" => "level",
                          "FirstName" => 'firstname',
                          "LastName" => 'lastname',
                          "Middle Name" => 'middlename',
                          "NickName" => "nick",
                          "Email Add" => "email_add",
                          "Address No" => "add_no",
                          "Street" => "add_street",
                          "BRGY" => "add_subdbrgy",
                          "Town" => "add_townctry",
                          "ZIP" => "add_zipcode",
                          "Homenumber" => "homenumber",
                          "Type" => "type",
                          "Subcription status" => "subcription_status",
                          "Number" => 'msisdn',
                      );

            $values = array();
            foreach($header as $key => $v) {
                $values[$v] = $val[$key] ;
            }

            $values['modified_by'] = $GLOBALS['login'];
            $lid = $this->insert('members',$values,"REPLACE");

            if($group && $group['id'] && $lid) {
                $values = array('member_id'=> $lid, 'group_id' => $group['id']);
                $this->insert('group_members',$values,"REPLACE");
            }
        }

    }

    function detail ($id) {
        $query = "SELECT * FROM members WHERE id='$id'";
        return $this->query($query, true);
    }

    /*
     * Add members and return the new member_id
     *
     */
    function ins($detail) {
        return $this->insert('members', $detail);
        return $this->last_insert_id('members');
    }

    /*
     * Update categories for a particular categories_id
     *
     */
    function edit ($id, $detail) {
        return $this->update('members', $detail, "id = '$id'");
    }

    /*
     * Delete members detail for a particular member_id
     *
     */
    function deactivate ($id) {
        $query = "UPDATE members SET active = '0' WHERE id='$id'";
        return $this->query($query, false);
    }
    function activate ($id) {
        $query = "UPDATE members SET active = '1' WHERE id='$id'";
        return $this->query($query, false);
    }

    function get_students($sy) {
        $sql = "SELECT s.stud_no,parentline,fname,mname,lname,l.description as level,
               se.name as section,l.name as level_code,ma.applied
               FROM enrollments e
               JOIN students s on e.student_id =s.id
               JOIN levels l on l.id =e.level_id
               LEFT JOIN section_offerings so on so.id =e.section_offering_id
               LEFT JOIN sections se on se.id =so.section_id
               LEFT JOIN miscellaneous_assessment ma ON ma.enrollment_id =e.id
               LEFT JOIN sy_level_miscellaneous sylm ON sylm.id =ma.sy_level_miscellaneous_id
               LEFT JOIN miscellaneous m ON m.id =sylm.miscellaneous_id
               LEFT JOIN payments p ON p.enrollment_id = e.id
               LEFT JOIN payment_details pd ON pd.payment_id = p.id
               WHERE e.school_year ='$sy' and LENGTH(parentline ) > 5
               AND m.name LIKE 'SMS%'
               AND ma.applied = '1'
               AND LOWER(pd.item) LIKE 'misc%'";
        /*SELECT s.stud_no,parentline,fname,mname,lname,l.description as level, se.name as section, l.name as level_code
                        FROM enrollments e
                        JOIN students s on e.student_id=s.id
                        JOIN levels l on l.id = e.level_id
                        LEFT JOIN section_offerings so on so.id = e.section_offering_id
                        LEFT JOIN sections se on se.id=so.section_id
                        WHERE e.school_year='$sy' and LENGTH(parentline) > 5 GROUP BY parentline";*/

        return $this->select_all($sql);
    }

    function get_employee() {
        return $this->select_all('SELECT * FROM  `employees` WHERE active=1');
    }
    function find_or_create_group($key) {
        $id = $this->select_one("SELECT id FROM groups WHERE name='$key'");
        if (empty($id)) {
            $d['name'] = $key;
            $d['description'] = $key;
            return $this->insert('groups', $d);
        } else {
            return $id[0];
        }
    }
}
